﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using System.Reflection;

/// <summary>
///dbHelper 的摘要说明
/// </summary>
public class dbHelper
{
	public dbHelper()
	{
		//
		//TODO: 在此处添加构造函数逻辑
		//
	}

    //执行存储过程后返回的对象
    public class pageproret
    {
        public int pagecount { get; set; } //分页总数
        public int totalcount { get; set; } //总记录数
        public DataTable recordset { get; set; } //记录集 
    }
    /// <summary>
    /// 防SQL注入
    /// </summary>
    /// <param name="source"></param>
    /// <returns></returns>
    public static string FilterSql(string source)
    {
        if (string.IsNullOrEmpty(source))
            return null;
        source = source.Replace("'", "''");
        //source = source.Replace(" ", "&nbsp;");
        source = source.Replace("\"", "&#34;");
        source = source.Replace(";", "&#59;");
        source = source.Replace("<", "&#34;");
        source = source.Replace(">", "&#62;");
        source = source.Replace("%", "&#37;");
        source = source.Replace("\\", "&#92;");
        source = source.Replace("--", "&#45;&#45;");
        return source;
    }
    #region 获得数据库连接

    private string _connectionStrings = string.Empty;
    public string ConnectionStrings
    {
        get
        {
            if (string.IsNullOrEmpty(_connectionStrings))
            {
                _connectionStrings = System.Configuration.ConfigurationManager.ConnectionStrings["SqlConn"].ToString();
            }
            return _connectionStrings;
        }        
    }

    public SqlConnection GetConnection()
    {
        string connectionStrings = System.Configuration.ConfigurationManager.ConnectionStrings["SqlConn"].ToString();

        SqlConnection myConn = new SqlConnection(connectionStrings);
        return myConn;
    }
    public void closeConn()
    {
        SqlConnection mycon = GetConnection();
        if (Convert.ToBoolean(mycon.State == ConnectionState.Open))
        {
            mycon.Close();
        }
    }

    #endregion


    public static void CheckIsLogin()
    {
        if (HttpContext.Current.Session["AdminUser"] == null || HttpContext.Current.Session["AdminUser"] == string.Empty)
        {
            HttpContext.Current.Response.Redirect("/Default.aspx");
        }
    }

    #region 获得cmd
    /// <summary>
    /// 执行存储过程，返回sqlCommand类对象
    /// </summary>
    /// <param name="strProcName"></param>
    /// <returns></returns>
    public SqlCommand GetCommandProc(string strProcName)
    {
        SqlConnection myConn = GetConnection();
        SqlCommand myCmd = new SqlCommand();
        myCmd.Connection = myConn;
        myCmd.CommandText = strProcName;
        myCmd.CommandType = CommandType.StoredProcedure;
        return myCmd;
    }
    /// <summary>
    /// 执行查询语句，返回sqlCommand类对象
    /// </summary>
    /// <param name="strSql">查询语句</param>
    /// <returns>返回sqlCommand类对象</returns>
    public SqlCommand GetCommandStr(string strSql)
    {
        SqlConnection myConn = GetConnection();
        SqlCommand myCmd = new SqlCommand();
        myCmd.Connection = myConn;
        myCmd.CommandText = strSql;
        myCmd.CommandType = CommandType.Text;
        return myCmd;
    }
    #endregion

    #region 获得DataReader
    /// <summary> 
    /// 获得一个DataReader,格式:GetDataReader(Sql命令) 
    /// </summary>
    /// <param name="SqlCommand"></param> 
    /// <param name="conn"></param> 
    /// <returns></returns> 
    public SqlDataReader GetDataReader(string procname, string sqlstr, SqlParameter[] cmdParms)
    {
        SqlCommand cmd = new SqlCommand();
        try
        {
            if (procname != null)
            {
                cmd = GetCommandProc(procname);
            }
            else
            {
                cmd = GetCommandStr(sqlstr);
            }
            runcmd(cmd, null, cmdParms);
            SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            cmd.Parameters.Clear();
            return rdr;
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message, ex);

        }
        finally
        {
              //if (cmd.Connection.State == ConnectionState.Open)
              //{
              //    cmd.Connection.Close();
                  //cmd.Connection.Dispose();

             // }
        }


    }
    #endregion

    #region 返回dataset对象


    /// <summary>
    /// 
    /// </summary>
    /// <param name="procname"></param>
    /// <param name="cmdParms"></param>
    /// <returns></returns>
    public DataSet returndataset(string procname, string sqlstr, SqlParameter[] cmdParms, string tbname)
    {



        SqlDataAdapter adapt = new SqlDataAdapter();
        DataSet ds = new DataSet();
        SqlCommand cmd = new SqlCommand();


        try
        {

            if (procname != null)
            {
                cmd = GetCommandProc(procname);
            }
            else
            {
                cmd = GetCommandStr(sqlstr);
            }
            runcmd(cmd, null, cmdParms);
            adapt = new SqlDataAdapter(cmd);
            if (tbname != null)
            {
                adapt.Fill(ds, "tbname");
            }
            else
            {
                adapt.Fill(ds);
            }
            cmd.Parameters.Clear();
            return ds;

        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message, ex);

        }
        finally
        {
            if (cmd.Connection.State == ConnectionState.Open)
            {
                cmd.Connection.Close();

            }
        }
    }

    #endregion

    #region 获得ExecuteScalar
    /// <summary> 
    /// 获得一个ExecuteScalar,格式:ExecuteScalar(Sql命令) 
    /// </summary> 
    /// <param name="SqlCommand"></param> 
    /// <param name="conn"></param> 
    /// <returns></returns>
    public string GetExecuteScalar(string procname, string sqlstr, SqlParameter[] cmdParms)
    {
        SqlCommand cmd = new SqlCommand();
        try
        {

            if (procname != null)
            {
                cmd = GetCommandProc(procname);
            }
            else
            {
                cmd = GetCommandStr(sqlstr);
            }
            runcmd(cmd, null, cmdParms);
            string val = cmd.ExecuteScalar().ToString();
            cmd.Parameters.Clear();
            return val;
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message, ex);

        }
        finally
        {
            if (cmd.Connection.State == ConnectionState.Open)
            {
                cmd.Connection.Close();

            }
        }



    }

    #endregion

    #region 执行sql并返回受影响行数,或存储过程的返回值 增删改

    /// <summary>
    /// 
    /// </summary>
    /// <param name="cmd"></param>
    /// <param name="cmdType"></param>
    /// <param name="cmdTxt"></param>
    public int GetExecuteNonQuery(string procname, string sqlstr, SqlParameter[] cmdParms)
    {
        SqlCommand cmd = new SqlCommand();
        try
        {
            if (procname != null)
            {
                cmd = GetCommandProc(procname);
            }
            else
            {
                cmd = GetCommandStr(sqlstr);
            }

            runcmd(cmd, null, cmdParms);
            int val = (int)cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return val;
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message, ex);

        }
        finally
        {
            if (cmd.Connection.State == ConnectionState.Open)
            {
                cmd.Connection.Close();

            }
        }
    }
    /// <summary>
    /// 获得存储过程的return值int
    /// </summary>
    /// <param name="procname"></param>
    /// <param name="cmdParms"></param>
    /// <returns></returns>
    public int Getprocreturn(string procname, SqlParameter[] cmdParms)
    {
        SqlCommand cmd = new SqlCommand();
        try
        {
            cmd = GetCommandProc(procname);

            runcmd(cmd, null, cmdParms);

            SqlParameter getreturn = new SqlParameter("@ret", SqlDbType.Int, 4); //定义返回值参数
            getreturn.Direction = ParameterDirection.ReturnValue;
            cmd.Parameters.Add(getreturn);

            cmd.ExecuteNonQuery();

            int getval = Convert.ToInt32(getreturn.Value);
            cmd.Parameters.Clear();

            return getval;
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message, ex);

        }
        finally
        {
            if (cmd.Connection.State == ConnectionState.Open)
            {
                cmd.Connection.Close();

            }
        }
    }

    #endregion

    #region 返回pageproret对象
    /// <summary>
    /// 
    /// </summary>
    /// <param name="procname"></param>
    /// <param name="cmdParms"></param>
    /// <returns></returns>
    public pageproret GetPageRecordSet(string tbname, int page, int pagesize, string pk, string sort, string fields, string filter)
    {
        SqlDataAdapter adapt = new SqlDataAdapter();
        DataSet ds = new DataSet();
        SqlCommand cmd = new SqlCommand();
        try
        {
            cmd = GetCommandProc("P_DbTable_RowCount_Paging");
            SqlParameter[] parms = new SqlParameter[] {
                new SqlParameter("@Tables",SqlDbType.NVarChar,1000),
                new SqlParameter("@PK",SqlDbType.NVarChar,100),
                new SqlParameter("@Sort",SqlDbType.NVarChar,200),
                new SqlParameter("@PageNumber",SqlDbType.Int,4),
                new SqlParameter("@PageSize",SqlDbType.Int,4),
                new SqlParameter("@Fields",SqlDbType.NVarChar,1000),
                new SqlParameter("@Filter",SqlDbType.NVarChar,1000)};
            parms[0].Value = tbname;
            parms[1].Value = pk;
            parms[2].Value = sort;
            parms[3].Value = page;
            parms[4].Value = pagesize;
            parms[5].Value = fields;
            parms[6].Value = filter;
            runcmd(cmd, null, parms);
            adapt = new SqlDataAdapter(cmd);
            adapt.Fill(ds);
            cmd.Parameters.Clear();
            DataTable rettb = ds.Tables[1];
            pageproret ret = new pageproret();
            ret.recordset = rettb;
            ret.pagecount = Convert.ToInt32(ds.Tables[0].DefaultView[0]["PageCount"].ToString());
            ret.totalcount = Convert.ToInt32(ds.Tables[0].DefaultView[0]["Count"].ToString());
            return ret;
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message, ex);
        }
        finally
        {
            if (cmd.Connection.State == ConnectionState.Open)
            {
                cmd.Connection.Close();

            }
        }
    }

    public pageproret GetPageRecordSet2(int page, int pagesize, string batchId, string strWhere)
    {
        SqlDataAdapter adapt = new SqlDataAdapter();
        DataSet ds = new DataSet();
        SqlCommand cmd = new SqlCommand();
        try
        {
            cmd = GetCommandProc("P_UserDataByBatchIdLog_Select");
            SqlParameter[] parameters = {					
					new SqlParameter("@BatchId", batchId),                   
                    new SqlParameter("@WhereStr", strWhere),                     
                    new SqlParameter("@pageindex", page),        
                    new SqlParameter("@pagesize", pagesize),     
                                    };
            runcmd(cmd, null, parameters);
            adapt = new SqlDataAdapter(cmd);
            adapt.Fill(ds);
            cmd.Parameters.Clear();
            DataTable rettb = ds.Tables[1];
            pageproret ret = new pageproret();
            
            ret.recordset = rettb;
            ret.pagecount = Convert.ToInt32(ds.Tables[2].DefaultView[0]["PageTotal"].ToString());
            ret.totalcount = Convert.ToInt32(ds.Tables[2].DefaultView[0]["RowTotal"].ToString());
            return ret;
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message, ex);
        }
        finally
        {
            if (cmd.Connection.State == ConnectionState.Open)
            {
                cmd.Connection.Close();

            }
        }
    }


    #endregion

    #region runcmd 函数
    /// <summary>
    /// runcmd 函数
    /// </summary>
    /// <param name="myCmd"></param>
    /// <param name="adapt"></param>
    /// <param name="ds"></param>
    private void runcmd(SqlCommand cmd, SqlTransaction trans, SqlParameter[] cmdParms)
    {


        if (cmd.Connection.State != ConnectionState.Open)
        {
            cmd.Connection.Open();
        }
        if (trans != null)
            cmd.Transaction = trans;

        if (cmdParms != null)
        {
            foreach (SqlParameter parm in cmdParms)
                cmd.Parameters.Add(parm);
        }



    }
    #endregion

    #region datatable转换list方法

    public T CreateItem<T>(DataRow row)
    {
        T obj = default(T);
        if (row != null)
        {
            obj = Activator.CreateInstance<T>();

            foreach (DataColumn column in row.Table.Columns)
            {
                PropertyInfo prop = obj.GetType().GetProperty(column.ColumnName);
                if (prop == null)
                {
                    continue;
                }

                try
                {
                    Type t = prop.PropertyType;

                    object value = row[column.ColumnName];
                    if (!(value == null || value == DBNull.Value))
                    {
                        switch (t.Name)
                        {
                            case "Int64":
                                value = Convert.ToInt64(value);
                                break;
                            case "Int32":
                                value = Convert.ToInt32(value);
                                break;
                            case "Int16":
                                value = Convert.ToInt16(value);
                                break;
                            case "ToBoolean":
                                value = Convert.ToBoolean(value);
                                break;
                            case "DateTime":
                                value = Convert.ToDateTime(value);
                                break;
                            case "Decimal":
                                value = Convert.ToDecimal(value);
                                break;
                            case "Double":
                                value = Convert.ToDouble(value);
                                break;
                            case "Byte":
                                value = Convert.ToByte(value);
                                break;
                            default:
                                value = Convert.ToString(value);
                                break;
                        }
                        prop.SetValue(obj, value, null);
                    }
                }
                catch
                {
                    throw;
                }
            }
        }

        return obj;
    }
    public IList<T> ConvertTo<T>(IList<DataRow> rows)
    {
        IList<T> list = null;

        if (rows != null)
        {
            list = new List<T>();

            foreach (DataRow row in rows)
            {
                T item = CreateItem<T>(row);
                list.Add(item);
            }
        }

        return list;
    }
    public IList<T> ConvertTo<T>(DataTable table)
    {

        if (table == null)
        {
            return null;
        }

        List<DataRow> rows = new List<DataRow>();

        foreach (DataRow row in table.Rows)
        {
            rows.Add(row);
        }

        return ConvertTo<T>(rows);
    }

    #endregion

    #region 分页函数

    public string BuildPager(int page, int page_count, int totalcount, string pager_style, string url_paras)
    {
        return string.Format("总记录数{0} {1}", totalcount,BuildPager( page,  page_count,  pager_style,  url_paras));
    }

    public string BuildPager(int page, int page_count, string pager_style, string url_paras)
    {
        string pageStr = "";
        string scriptname = HttpContext.Current.Request.Url.ToString().Split('?')[0];
        if (page > 1)
        {
            pageStr += "<a class='" + pager_style + "' href='?page=1&" + url_paras + "'>首页</a> <a class='" + pager_style + "' href='?page=" + (page - 1) + "&" + url_paras + "'>前一页</a>&nbsp;&nbsp;";
        }

        if (page < page_count)
        {
            pageStr += "<a class='" + pager_style + "' href='?page=" + (page + 1) + "&" + url_paras + "'>后一页</a> <a class='" + pager_style + "' href='?page=" + page_count + "&" + url_paras + "'>末页</a>&nbsp;&nbsp;&nbsp;";
        }

        if (page_count > 3 && page_count <= 80)
        {
            pageStr += "<select name='sltpage' style='font-size:10px;font-family:arial' onchange=window.open('" + scriptname + "?page='+this.value+'&" + url_paras + "','_self')>";
            for (int i = 1; i <= page_count; i++)
            {
                pageStr += "<option value='" + i + "'";
                if (i == page)
                    pageStr += " selected";
                pageStr += ">" + i + "</option>";
            }
            pageStr += "</select>";
            pageStr += "<span style='font:10px arial;color:#333333;'> / " + page_count + "</span>";
        }
        else if (page_count > 80)
        {
            if (page > page_count)
            {
                HttpContext.Current.Response.Write("参数错误");
                HttpContext.Current.Response.End();
            }
            else
            {
                pageStr += "<input type='text' id='jump_page_num' style='font:10px arial;width:25px;height:14px;border:solid #aaaaaa 1px;' value='" + page + "'><span style='font:10px arial;color:#333333;'> / " + page_count + "</span>&nbsp;";
                pageStr += "<input type='button' id='btnJump' value='>>' style='font:9px arial;margin:1px;height:14px;border:solid #aaaaaa 1px;' onclick=window.open('" + scriptname + "?page='+document.getElementById('jump_page_num').value+'&" + url_paras + "','_self')>";
            }
        }
        return pageStr;
    }

    #endregion
}
